from __future__ import (absolute_import, division, print_function,
                        unicode_literals)
import datetime  # For datetime objects
import os.path  # To manage paths
import warnings
import pandas as pd
import backtrader as bt
import json

import sqlalchemy

import okex.v5.market_api as market
from strategies import macdStrategy
from tools.dingdingrobot import call_dingtalk
import time
from sqlalchemy import create_engine
warnings.filterwarnings("ignore")

test = True
def get_history_candles_engine(marketAPI, instId, engine,days=1, bar='4H',table_name = "default",from_time_stamp = None,to_time_stamp = None):
    """

    :param marketAPI:
    :param instId:
    :param days:
    :param bar:
    :return:
    """
    call_dingtalk("获取历史数据开始", 'text', test=test)
    # 获取当前时间的毫秒级时间戳
    now = datetime.datetime.now()
    timestamp_now = int(now.timestamp() * 1000)
    from_time = None
    if from_time_stamp ==None:
        # 获取两个月之前的时刻并将其转换为毫秒级时间戳
        two_months_ago = now - datetime.timedelta(days=days)
        from_time = int(two_months_ago.timestamp() * 1000)
    else:
        from_time = from_time_stamp

    history_candles = marketAPI.history_candles(instId, before=from_time,
                                                after=timestamp_now, bar=bar)
    if len(history_candles) == 0:
        return history_candles
    print("写入数据库开始")
    history_candles.to_sql(table_name, con=engine, if_exists='append', index=True)
    print("写入数据库成功")
    time_obj = history_candles.iloc[-1, 0]
    timestamp = int(datetime.datetime.strptime(str(time_obj), '%Y-%m-%d %H:%M:%S').timestamp()*1000)

    old_timestamp = None
    while timestamp - from_time:
        time.sleep(2)
        try:
            more_history_candles = marketAPI.history_candles(instId, before=from_time,
                                                                after=timestamp, bar=bar)
            # 写入数据库
            print("写入数据库开始")
            more_history_candles.to_sql(table_name, con=engine, if_exists='append', index=True)
            print("写入数据库成功")
        except:
            time_obj = history_candles.iloc[-1, 0]
            print("获取数据失败，时间为{}".format(str(time_obj)))
            continue
        if len(more_history_candles) == 0:
            time_obj = history_candles.iloc[-1, 0]
            print("获取数据结束，时间为{}".format(str(time_obj)))
            break
        history_candles = pd.concat([history_candles, more_history_candles])
        history_candles.reset_index(drop=True, inplace=True)

        time_obj = history_candles.iloc[-1, 0]
        print("当前获取的时间为{}".format(str(time_obj)))
        timestamp = int(datetime.datetime.strptime(str(time_obj), '%Y-%m-%d %H:%M:%S').timestamp()*1000)
        if old_timestamp == None:
            old_timestamp = timestamp
        else:
            if old_timestamp == timestamp:
                break
            else:
                old_timestamp = timestamp
        print("total data length is {}".format(len(history_candles)))
    return history_candles



if __name__ == '__main__':

    base_dir = r"../"

    with open(os.path.join(base_dir, "passwords", "passwords.json"), 'r') as f:
        data = json.load(f)

    if test:
        # 模拟盘api
        api_key = data[0]['api_key']
        secret_key = data[0]['secret_key']
        passphrase = data[0]['passphrase']
    else:
        # 实盘API
        api_key = data[1]['api_key']
        secret_key = data[1]['secret_key']
        passphrase = data[1]['passphrase']

    obj = dict([('api_key', api_key), ('secret_key', secret_key), ('passphrase', passphrase)])

    api_key = obj['api_key']
    secret_key = obj['secret_key']
    passphrase = obj['passphrase']

    instId = 'BTC-USDT-SWAP'
    # 市场行情API
    marketAPI = market.MarketAPI(api_key, secret_key, passphrase, use_server_time=False, test=test)
    marketAPI.set_api_url("https://www.okx.com")
    engine = create_engine('mysql+pymysql://root:321ewqdsacxz@localhost:3306/BTC_HISTORY')

    # 编写SQL查询语句
    table_name = 'btc_usdt_swap_5_year'
    sql_query = "SELECT * FROM "+table_name
    days = 365 #增加的时间
    if sqlalchemy.inspect(engine).has_table(table_name):
        real_time_history_df = pd.read_sql_query(sql_query, engine)
        # 删除重复行
        real_time_history_df.drop_duplicates(subset=['ts'], keep='first', inplace=True)
        # 根据ts排序
        real_time_history_df.sort_values(by='ts', ascending=False,inplace=True)
        latest_time = real_time_history_df.iloc[0,:]['ts']
        oldest_time = real_time_history_df.iloc[-1,:]['ts']
        from_time_stamp = int(datetime.datetime.strptime(str(latest_time), '%Y-%m-%d %H:%M:%S').timestamp()*1000)
        get_history_candles_engine(marketAPI,instId,engine,days=days,table_name=table_name,bar='1m',from_time_stamp=from_time_stamp)
    real_time_history_df = pd.read_sql_query(sql_query,engine)
    # 删除重复行
    real_time_history_df.drop_duplicates(subset=['ts'], keep='first', inplace=True)
    # 根据ts排序
    real_time_history_df.sort_values(by='ts', ascending=False, inplace=True)

    real_time_history_df = real_time_history_df.loc[:,['ts','o','h','l','c']]

